CREATE DATABASE [CERTIFICATES]
GO
USE [CERTIFICATES]

CREATE TABLE STUDENT
(
	STUDENTID INT IDENTITY(1,1) PRIMARY KEY,
	STUDENTNAME NVARCHAR(50),
	[ADDRESS] NVARCHAR(500),
	DATEOFBIRTH DATETIME ,
	GENDER NVARCHAR(10),
	COURSEID INT,
	USERNAME NVARCHAR(50)
)
CREATE TABLE COURSE
(
	COURSEID INT IDENTITY(1,1) PRIMARY KEY,
	COURSENAME NVARCHAR(30)
	
)
CREATE TABLE [SUBJECT]
(
	SUBJECTID INT IDENTITY(1,1) PRIMARY KEY,
	SUBJECTNAME NVARCHAR(30),
	COURSEID INT
)
CREATE TABLE PAYMENT
(
	PAYMENTID INT IDENTITY(1,1) PRIMARY KEY,
	STUDENTID INT,
	COURSEID INT,
	TOTALFEES MONEY,
	PAID MONEY,
	BALANCE MONEY,
	FEES_STRUCTURE BIT
)
CREATE TABLE MARKS
(
	MARKID INT PRIMARY KEY,
	COURSEID INT,
	STUDENTID INT ,
	MARKOBTAINED INT,
	OUTOFMARK INT,
	[STATUS] NVARCHAR(30)
)
CREATE TABLE [LOGIN]
(
	USERNAME NVARCHAR(50) PRIMARY KEY,
	[PASSWORD] NVARCHAR(50),
	[ROLE] INT
)
CREATE TABLE [CERTIFICATES]
(
	STUDENTID INT IDENTITY(1,1) PRIMARY KEY,
	COURSEID INT,
	GRADE NVARCHAR(30)
)
ALTER TABLE STUDENT ADD CONSTRAINT FK_STU_COUR FOREIGN KEY (COURSEID) REFERENCES COURSE(COURSEID) 
ALTER TABLE [SUBJECT] ADD CONSTRAINT FK_SUB_COUR FOREIGN KEY (COURSEID) REFERENCES COURSE(COURSEID)
ALTER TABLE PAYMENT ADD CONSTRAINT FK_PAY_STU FOREIGN KEY (STUDENTID) REFERENCES STUDENT(STUDENTID)
ALTER TABLE PAYMENT ADD CONSTRAINT FK_PAY1_COUR FOREIGN KEY (COURSEID) REFERENCES COURSE(COURSEID)
ALTER TABLE MARKS ADD CONSTRAINT FK_MAR_COUR FOREIGN KEY (COURSEID) REFERENCES COURSE(COURSEID)
ALTER TABLE MARKS ADD CONSTRAINT FK_MAR_STU FOREIGN KEY (STUDENTID) REFERENCES STUDENT(STUDENTID)
ALTER TABLE [CERTIFICATES] ADD CONSTRAINT FK_CER_COUR FOREIGN KEY (COURSEID) REFERENCES COURSE(COURSEID)
ALTER TABLE [CERTIFICATES] ADD CONSTRAINT FK_CER_STU FOREIGN KEY (STUDENTID) REFERENCES STUDENT(STUDENTID)
ALTER TABLE STUDENT ADD CONSTRAINT FK_STU_LOG FOREIGN KEY (USERNAME) REFERENCES [LOGIN](USERNAME) 
